An example of a data index to navigate through metadata and preview images

Author

G.Fraga Gonzalez

Published

February 12, 2024

This is a demo of a data index that allows to navigate through a table with metadata and thumbnail images. The idea is to facilitate finding and sharing your data. Visit our Tutorials page for more details on this workflow. In short, for the index you need: (1) a source of structured metadata (2) if you are to display thumbnails you need the picture files (3) some R code to render the table interactive with the package DT and (4) R Quarto to output the table as an HTML page

This demo used a dummy dataset and the source metadata table and images have no meaning. The example demonstrates three frequent operations that can be useful for creating a data index:

  • Reading filenames and extracting information into columns from filename parts

  • Joining two tables according to a key variable with shared values between them

  • Adding some HTML code to a column values (in this case filenames) to render the images (the same approach would be done to render hyperlinks).

Instructions

Use the filter boxes to select which data are displayed. You can also select Columns to choose which columns are displayed or change the column order if you click and drag on the variable names. You can also select rows (one or multiple) clicking on them or by using the select button. Click on a thumbnail to open the full-size image. Click the button Copy to copy the filtered or selected rows to the clipboard. With the buttons csv or pdf you can save them to these two formats.

Code
library(crosstalk) # for filter boxes
library(DT) # For html table

# first we need an object that will be shared by filter panels and datatable 
shared_joined <- SharedData$new(tbl_joined, key = ~subjID, group = "shared_obj")

# to make two columns: one with filter panels and one with the table
bscols(widths = c(2,10),
     device = c("xs", "sm", "md", "lg"),

# filter panels. Other formats are sliders and checkboxes https://rstudio.github.io/crosstalk/using.html
list(
 filter_select( id = "subjID", label = "subject",sharedData = shared_joined, group = ~subjID),
 filter_checkbox("Sex","Sex",shared_joined, ~Sex, inline = FALSE)
),

# table
datatable(
    shared_joined,
    #filter = "top",
    escape = FALSE,
    rownames = FALSE,
    width = "100%",
    class = 'compact cell-border  hover', 
    extensions = c('Buttons', 'Select','ColReorder', 'Scroller',  'KeyTable'),
    selection = 'none',
    options = list(
      pageLength = 10,
      dom = 'Bfrtip',
      #buttons = c('colvis','selectAll', 'selectNone', 'copy', 'csv', 'pdf'),  
      buttons = list(list(extend = "colvis", text = "select Columns", background='yellow'),
                     'selectAll', 'selectNone', 'copy', 'csv', 'pdf'), 
      select = list(style = 'os', items = 'row'),
      scrollX = TRUE,
      scrollCollapse = FALSE,  
      autoWidth = TRUE,
      colReorder = TRUE,
      columnDefs = list(
        list(
          keys = TRUE,
          search = list(regex = TRUE),
          targets=0
        )
      )
      )
  ) 
)

The following steps will vary depending on the index content. Steps 1 to 4 will change depending on our metadata tables and if we want to render links, images, etc. Step 5 will vary depending on the many possible options we have to display the table and filters. For this example we did:

1. Read metadata table

We first read a source metadata table with information about subjects and experiments (image recording)

Code
library(dplyr)
tbl <-  readxl::read_excel('DummyData1_20241234_subjects.xlsx',na=c("","N/A"))

# Minor adjustment of time format
colsWithTime <- colnames(tbl)[grep('*time*',colnames(tbl))] # find variables with 
tbl <- tbl %>%  mutate(across(all_of(colsWithTime), ~ format(., format = "%H:%M")))

2. Make a table with filenames

We read the names of files with the images we want to show case. The files with subject images start with subject identifier separated by the rest by a hyphen ‘-’. We use this to create a new variable containing the subject identifier of each image file

Code
files <- dir('Images') # Find all files in our images folder
fname <- files[grepl(paste0('*.jpg$'),files)] #take only .jpg files

tbl_files <- as.data.frame(fname) # make table with filenames
tbl_files$subject <- sapply(strsplit(fname,'-'),'[[',1) #take 1st filename part

3. Join the two tables

Now we link the image filenames to the source metadata table

Code
tbl_joined  <- full_join(x=tbl,
                         y = tbl_files,
                         by=join_by("subjID"=="subject"),
                         keep=FALSE)

4. Add HTML code to render the images

Since we want to display images we will add some HTML with the address of the image files (the repository). The URL is added as href so that we access the image when we click. The path relative to this script is added to src so that the table renders the image thumbnail.

Code
# define image paths
pic_fullpath <- file.path('https://gitlab.uzh.ch/crsuzh/afford_website/-/tree/master/Docs/contents/ORD_index/Images',tbl_joined$fname)
pic_relpath <- file.path('..','..','Images',tbl_joined$fname)

# add paths and HTML code 
tbl_joined$pic <- paste0('<a href=\'', pic_fullpath,'\' target=\'_blank\'>', 
                       '<img src=\'',pic_relpath, '\' height=\'70\'></a>')

# Move to first column
tbl_joined <- tbl_joined %>% relocate("pic",.before = 1)

getwd()
[1] "C:/Users/gorka/Gitlab_crs/AFFORD_website/Docs/contents/ORD_index"

5. Render the HTML table with DT package

Finally we render the table interactive and add some custom filter boxes. We need two packages (they were installed in advanced with install.packages() and then we load their libraries with the command library()). The main package is DT which allows to render the table interactive. The R package crosstalk is used to allow filter boxes to change what rows we see in the table. Its function bscols() allows to lay out the filters on a column next to the table.

Code
library(crosstalk) # for filter boxes
library(DT) # For html table

# first we need an object that will be shared by filter panels and datatable 
shared_joined <- SharedData$new(tbl_joined, key = ~subjID, group = "shared_obj")

# to make two columns: one with filter panels and one with the table
bscols(widths = c(2,10),
     device = c("xs", "sm", "md", "lg"),

# filter panels. Other formats are sliders and checkboxes https://rstudio.github.io/crosstalk/using.html
list(
 filter_select( id = "subjID", label = "subject",sharedData = shared_joined, group = ~subjID),
 filter_checkbox("Sex","Sex",shared_joined, ~Sex, inline = FALSE)
),

# table
datatable(
    shared_joined,
    #filter = "top",
    escape = FALSE,
    rownames = FALSE,
    width = "100%",
    class = 'compact cell-border  hover', 
    extensions = c('Buttons', 'Select','ColReorder', 'Scroller',  'KeyTable'),
    selection = 'none',
    options = list(
      pageLength = 10,
      dom = 'Bfrtip',
      #buttons = c('colvis','selectAll', 'selectNone', 'copy', 'csv', 'pdf'),  
      buttons = list(list(extend = "colvis", text = "select Columns", background='yellow'),
                     'selectAll', 'selectNone', 'copy', 'csv', 'pdf'), 
      select = list(style = 'os', items = 'row'),
      scrollX = TRUE,
      scrollCollapse = FALSE,  
      autoWidth = TRUE,
      colReorder = TRUE,
      columnDefs = list(
        list(
          keys = TRUE,
          search = list(regex = TRUE),
          targets=0
        )
      )
      )
  ) 
)
Back to top